package com.cdl.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cdl.utils.bean.ColumnInfo;
import com.cdl.utils.bean.IndexInfo;



/****
 * 
 * 
 * JDBC工具类
 * @author cdl
 *
 */
public class JDBCUtil {
	

	static{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/***
	 * 获取到数据库链接
	 * @return
	 */
	public static Connection getConnection(){
		Connection conn = null ;
		try {
			
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@197.97.1.19:1521:ora11","hzbsbdb","hzbsbdb");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return conn ;
	}
	
    /***
     * 根据表名获取表的字段信息
     * @param TableName
     * @throws SQLException
     */
	public static List<ColumnInfo> getTableColumns(String TableName) {
		
		StringBuffer sql =  new StringBuffer();
		sql.append(" select col.column_name, col.data_type, col.data_length,col.data_scale, con.COMMENTS");
	    sql.append(" from user_tab_columns col, user_col_comments con");
		sql.append(" where col.TABLE_NAME = con.TABLE_NAME");
		sql.append("  and col.COLUMN_NAME = con.COLUMN_NAME");
		sql.append(" and col.table_name = '"+ TableName +"'");
		
		List<ColumnInfo> cols = new  ArrayList<ColumnInfo>();
		
				
		Connection conn = null ;
		Statement st = null ;
		ResultSet set = null ;
		try {
			conn = getConnection();
			st =  conn.createStatement();
			set = st.executeQuery(sql.toString());
			//获取所列信息
			while(set.next()){
				ColumnInfo col = new ColumnInfo();
				col.setColumnName(set.getString(1));
				col.setColumnType(set.getString(2));
				col.setColumnLength(set.getInt(3));
				col.setColumnScale(set.getInt(4));
				col.setColumnComments(set.getString(5));
				cols.add(col);
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
		}
		return cols ;
	}
	
	
	/***
	 * 根据表名获取表的索引信息
	 * @param TableName
	 * @throws SQLException
	 */
	public static List<IndexInfo> getTableIndexs(String TableName) {
		
		StringBuffer sql =  new StringBuffer();
		sql.append("    select ic.index_name         												");
		sql.append("       , ic.column_name column_names 											");
		sql.append("      , case 																	");
		sql.append("           when i.UNIQUENESS = 'NONUNIQUE' then 0 								");
		sql.append("           when i.UNIQUENESS = 'UNIQUE' then 1    								");
		sql.append("         end isUnique  															");
		sql.append("    from (select index_name, to_char(wm_concat(column_name)) column_name		");
		sql.append("            from user_ind_columns												");
		sql.append("           where table_name = '"+ TableName +"'									");
		sql.append("           group by index_name) ic, user_indexes i								");
		sql.append("   where ic.index_name = i.index_name											");
		
		List<IndexInfo> indexs = new  ArrayList<IndexInfo>();
		Connection conn = null ;
		Statement st = null ;
		ResultSet set = null ;

		try {
			conn = getConnection();
			st =  conn.createStatement();
			set = st.executeQuery(sql.toString());
			while(set.next()){
				IndexInfo ind = new IndexInfo();
				ind.setIndexName(set.getString(1));
				ind.setColNames(set.getString(2));
				ind.setUnique(set.getBoolean(3));	
				indexs.add(ind);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn, st, set);
		}
		return indexs ;
	}
	
	/***
	 * 
	 * 关闭连接
	 * @param conn
	 * @param st
	 * @param set
	 */
	public static void close(Connection conn,Statement st,ResultSet set){

			try {
				if(conn != null){
					conn.close();
				}
				if(st != null){
					st.close();
				}
				if(set != null){
					set.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

	}
	
	
	
	public static void main(String[] args) throws SQLException {

		List<IndexInfo> l = getTableIndexs("USERINFO");
		System.out.println(l);
		for(IndexInfo i:l){
			System.out.println(i.toString());
		}
	}
	
	
	
	
}
